package com.fastgeek.platform.db.executor;


import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.wmccn.persistence.build.model.PreStatementSQL;
import com.wmccn.persistence.datasource.DataSourceFactory;
import com.wmccn.persistence.datasource.model.DataSourceMeta;
import com.wmccn.persistence.exception.DBException;
import com.wmccn.persistence.executor.model.ConnectionStatus;
import com.wmccn.platform.util.Debug;
import com.wmccn.platform.util.ThreadUtil;

public class SqlExecutor {
	public static final String MODULE = SqlExecutor.class.getName();

	/**
	 * Statistics number of records
	 * 
	 * @param ds
	 * @param sql
	 * @return
	 */
	public static int count(String ds, String sql) {
		Integer count = (Integer) queryOne(ds,
				"select count(*) N from (" + sql + ")").get("N");
		return count;
	}

	/**
	 * Query a single record
	 * 
	 * @param ds
	 * @param sql
	 * @return
	 */
	public static Map queryOne(String ds, String sql) {
		List<Map> rsList = query(ds, sql);
		return rsList != null && rsList.size() > 0 ? rsList.get(0) : null;
	}

	/**
	 * Query list records
	 * 
	 * @param ds
	 * @param sql
	 * @return
	 */
	public static List<Map> query(String ds, String sql) {
		List<Map> rtnList = new ArrayList<Map>();

		Statement stmt = null;
		ResultSet rs = null;
		Connection connection = null;
		try {
			connection = openNewConnection(ds);
			stmt = connection.createStatement();
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();

			Map bean = null;
			while (rs.next()) {
				bean = new HashMap();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					if (rsmd.getColumnType(i) == Types.BLOB
							|| rsmd.getColumnType(i) == Types.LONGVARBINARY) {
						bean.put(rsmd.getColumnName(i), rs.getBytes(i));
					} else if (rsmd.getColumnType(i) == Types.NUMERIC) {
						bean.put(rsmd.getColumnName(i), rs.getInt(i));
					} else {
						String tmpStr = rs.getString(i);
						bean.put(rsmd.getColumnName(i), tmpStr == null ? ""
								: tmpStr);
					}
				}
				rtnList.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rtnList;
	}

	/**
	 * Executing SQL statements
	 * 
	 * @param ds
	 * @param sql
	 */
	public static void execute(String ds, String sql) {
		Statement statement = null;
		Connection connection = null;
		boolean isTrans = false;
		try {
			ConnectionStatus connStatus = openConnection(ds);
			connection = connStatus.getConnection();
			isTrans = connStatus.isTrans();
			statement = connection.createStatement();
			statement.execute(sql);
		} catch (SQLException e) {
			Debug.logError(e, "execute(ds:" + ds + ",sql:" + sql + ")", MODULE);
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
				if (connection != null && !isTrans) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void execute(String ds, List<String> sql) {
		Statement statement = null;
		Connection connection = null;
		boolean isTrans = false;
		try {
			ConnectionStatus connStatus = openConnection(ds);
			connection = connStatus.getConnection();
			isTrans = connStatus.isTrans();
			statement = connection.createStatement();
			for (String tmp : sql) {
				statement.addBatch(tmp);
			}
			statement.executeBatch();
		} catch (Exception e) {
			Debug.logError(e, "execute(ds:" + ds + ",sql:" + sql + ")", MODULE);
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
				if (connection != null && !isTrans) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * Executing SQL statements
	 * 
	 * @param ds
	 * @param preSql
	 */
	public static void execute(String ds, PreStatementSQL preSql) {
		PreparedStatement stmt = null;
		Connection connection = null;
		boolean isTrans = false;

		try {
			ConnectionStatus connStatus = openConnection(ds);
			connection = connStatus.getConnection();
			isTrans = connStatus.isTrans();

			stmt = connection.prepareStatement(preSql.getSql());
			for (Object[] tmpRow : preSql.getPlaceValue()) {
				int i = 1;
				for (Object tmpPlaceVal : tmpRow) {
					if (tmpPlaceVal instanceof byte[]) {
						byte[] value = (byte[]) tmpPlaceVal;
						stmt.setBinaryStream(i++, new ByteArrayInputStream(
								value), value.length);
					} else if (tmpPlaceVal instanceof String) {
						stmt.setString(i++, (String) tmpPlaceVal);
					} else if (tmpPlaceVal instanceof Integer) {
						stmt.setInt(i++, (Integer) tmpPlaceVal);
					}
				}
				stmt.addBatch();
			}
			stmt.executeBatch();
		} catch (SQLException e) {
			Debug.logError(e);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
				if (connection != null && !isTrans) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * Open the data source connection
	 * 
	 * @param dsName
	 * @return
	 * @throws SQLException 
	 */
	private static ConnectionStatus openConnection(String dsName) throws SQLException {
		Connection connection = null;
		Transaction trans = (Transaction) ThreadUtil
				.threadVar(Transaction.class.getName());
		if (trans != null) {
			return new ConnectionStatus(trans.getConnection(dsName), true);
		} else {
			return new ConnectionStatus(openNewConnection(dsName), false);
		}
	}

	/**
	 * Open the data source connection
	 * 
	 * @param dsName
	 * @return
	 * @throws SQLException
	 */
	private static Connection openNewConnection(String dsName)
			throws SQLException {
		DataSourceMeta meta = DataSourceFactory.getInstance()
				.getDataSourceMeta(dsName);
		return meta.getConnection();
	}
}
